<?php defined('BASEPATH') || exit('No direct script access allowed');

require(APPPATH.'/libraries/UploadHandler.php');
require(APPPATH.'/libraries/PHPExcel.php');
//require(APPPATH.'/libraries/PYInitials.php');
require(APPPATH.'/libraries/Pinyin.php');

/**
 * 销售管理控制器
 *
 * @return void
 */
class Sale extends Auth_Controller
{

	public function __construct()
	{
		parent::__construct();
		$this->load->model('sale_model','',TRUE);
        $this->load->language('clue');
        $this->load->language('track');

        $this->load->library('form_validation');

        // 取出所有教育顾问
		if($this->_adminer['group'] == 4) {
			$this->_data['consultants'] = array($this->_adminer);
		} else {
			$this->_data['consultants'] = $this->user_model->get_all_users(4);
		}
	}

	public function reception()
	{
		$this->_data['page_title'] = '上门接待';

		$this->load->language('clue');
		$this->load->language('track');

		

		$clueTable              = 'students';
		$userTable              = 'users';
		$trackTable              = 'tracks';
		$userKey                = 'id';
		$trackKey                = 'track_id';
		$clueKey                = 'student_id';

		$where = array(
			"{$clueTable}.type"=>0,
			"{$clueTable}.deleted"=>0,
			"{$clueTable}.status"=>0,
			"{$trackTable}.track_result"=>2);

		if( isset($_GET['consultant'])&&!empty($_GET['consultant']))
		{
			$where["{$clueTable}.consultant"] = $_GET['consultant'];
		} else {
			if($this->_adminer['group'] == 4) {
				$where["{$clueTable}.consultant"] = $this->_adminer[
				'id'];
			}
		}
		if( isset($_GET['letter'])&&!empty($_GET['letter']))
		{
			$where["LEFT(`bf_{$clueTable}`.`student_name_letter`,1)"] = strtolower($_GET['letter']);
		}

		if( isset($_GET['phone'])&&!empty($_GET['phone']))
		{
			$where["{$clueTable}.phone"] = strtolower($_GET['phone']);
		}

		if( isset($_GET['clue_source'])&&!empty($_GET['clue_source']))
		{
			$where["{$clueTable}.source"] = strtolower($_GET['clue_source']);
		}

		if( isset($_GET['trade_wish'])&&!empty($_GET['trade_wish']))
		{
			$where["{$clueTable}.trade_wish"] = strtolower($_GET['trade_wish']);
		}

		if( isset($_GET['from'])&&!empty($_GET['from'])&&isset($_GET['to'])&&!empty($_GET['to']))
		{
			$where["{$clueTable}.last_contact_date >"] = $_GET['from'];
			$where["{$clueTable}.last_contact_date <"] = $_GET['to'];
		}

		if( isset($_GET['student_name'])&&!empty($_GET['student_name']))
		{
			$where["{$clueTable}.student_name"] = trim($_GET['student_name']);
		}

		if( isset($_GET['clue_stat'])&&!empty($_GET['clue_stat']))
		{
			$where["{$clueTable}.status"] = trim($_GET['clue_stat']);
		}

		if( isset($_POST['contact_times'])&&!empty($_POST['contact_times']))
		{
			if($_POST['contact_times']>=0&&$_POST['contact_times']<=5) {
				$where["{$clueTable}.contact_times"] = intval($_POST['contact_times']);
			} else if($_POST['contact_times']==6) {
				$where["{$clueTable}.contact_times >="] = 6;
				$where["{$clueTable}.contact_times <="] = 10;
			} else if($_POST['contact_times']==10) {
				$where["{$clueTable}.contact_times >"] = 10;
			}
		}

		$this->_data['clues'] = $this->sale_model->get_all_clues($where);

		$this->load->view('sale/reception_lists',$this->_data);
	}

	// 线索管理
	public function clues()
	{
		$this->_data['page_title'] = '线索管理';

		$this->load->language('clue');

		$this->_data['message'] = $this->session->flashdata('message');

		$this->load->view('sale/clue_lists',$this->_data);
	}

	// 线索管理datatable
	public function clue_db()
	{
		ob_start();

		$clueTable              = 'bf_students';
		$userTable              = 'bf_users';
		$userKey                = 'id';

		$where = array("{$clueTable}.type"=>0,"{$clueTable}.deleted"=>0);

		if( isset($_POST['consultant'])&&!empty($_POST['consultant']))
		{
			$where["{$clueTable}.consultant"] = $_POST['consultant'];
		} else {
			if($this->_adminer['group'] == 4){
				$where["{$clueTable}.consultant"] = $this->_adminer['id'];
			}
		}

		if( isset($_POST['letter'])&&!empty($_POST['letter']))
		{
			$where["LEFT(`{$clueTable}`.`student_name_letter`,1)"] = strtolower($_POST['letter']);
		}

		if( isset($_POST['phone'])&&!empty($_POST['phone']))
		{
			$where["{$clueTable}.phone"] = strtolower($_POST['phone']);
		}

		if( isset($_POST['grade'])&&!empty($_POST['grade']))
		{
			$where["{$clueTable}.grade"] = strtolower($_POST['grade']);
		}

		if( isset($_POST['clue_source'])&&!empty($_POST['clue_source']))
		{
			$where["{$clueTable}.source"] = strtolower($_POST['clue_source']);
		}

		if( isset($_POST['trade_wish'])&&!empty($_POST['trade_wish']))
		{
			$where["{$clueTable}.trade_wish"] = strtolower($_POST['trade_wish']);
		}

		if( isset($_POST['from'])&&!empty($_POST['from'])&&isset($_POST['to'])&&!empty($_POST['to']))
		{
			$where["{$clueTable}.last_contact_date >"] = $_POST['from'];
			$where["{$clueTable}.last_contact_date <"] = $_POST['to'];
		}

		if( isset($_POST['student_name'])&&!empty($_POST['student_name']))
		{
			$where["{$clueTable}.student_name"] = trim($_POST['student_name']);
		}

		if( isset($_POST['next_contact_date'])&&!empty($_POST['next_contact_date']))
		{
			$where["{$clueTable}.next_contact_date"] = trim($_POST['next_contact_date']);
		}

		if( isset($_POST['clue_stat'])&&!empty($_POST['clue_stat']))
		{
			$where["{$clueTable}.status"] = trim($_POST['clue_stat']);
		}

		if( isset($_POST['contact_times'])&&!empty($_POST['contact_times']))
		{
			if($_POST['contact_times']>=0&&$_POST['contact_times']<=5) {
				$where["{$clueTable}.contact_times"] = intval($_POST['contact_times']);
			} else if($_POST['contact_times']==6) {
				$where["{$clueTable}.contact_times >="] = 6;
				$where["{$clueTable}.contact_times <="] = 10;
			} else if($_POST['contact_times']==10) {
				$where["{$clueTable}.contact_times >"] = 10;
			}	
		}

//		var_dump($where);

		$this->load->library('datatables');
		$this->load->helper('clue_helper');

		$grade = config_item('comm.grade');
		$wish = config_item('comm.trade_wish');
		$status = config_item('comm.clue_status');

		$this->datatables->select("{$clueTable}.student_id as student_id,{$clueTable}.student_name as student_name,
			{$clueTable}.parent_name1 as parent_name1,{$clueTable}.trade_wish as trade_wish,{$clueTable}.phone as phone,
			{$clueTable}.school as school,{$clueTable}.grade as grade,{$clueTable}.contact_times as contact_times,
			{$clueTable}.last_contact_date as last_contact_date,{$clueTable}.last_contact_result as last_contact_result,	
			{$clueTable}.next_contact_date as next_contact_date,{$clueTable}.status as status,
			{$userTable}.nickname")
						->join($userTable, "{$clueTable}.consultant = {$userTable}.{$userKey}", 'left')
						->add_column('checkbox','<input type="checkbox" class="checkboxes" value="$1">','student_id')
						->edit_column('student_name','$1','generate_name(student_id,student_name)')
						->edit_column('trade_wish','$1','generate_trade_wish(trade_wish)')
						->edit_column('grade','$1','generate_grade(grade)')
						->edit_column('status','$1','generate_status(status)')
						->add_column('student_no','$1','generate_no(student_id)')
						->add_column('actions','$1','generate_actions(consultant,student_id,student_name)')
						->where($where)
						->from('students');

		echo $this->datatables->generate();
	}

    // 创建线索
    public function clues_create()
    {
        $this->_data['page_title'] = '新增线索';
        if (isset($_POST['save']))
        {
                if ($id = $this->_save_clue($_POST))
                {
 					$this->session->set_flashdata('message',set_message('success','添加成功'));                	
 					redirect('sale/clues','refresh');
                }
        }

        $this->load->view('sale/clue_form',$this->_data);
    }

	// 线索编辑
	public function clue_view()
	{
		$clue_id = $this->uri->segment(3);
		$this->_data['page_title'] = '编辑线索';
		// 获取单个跟踪
		$clue = $this->sale_model->get_clue_by_id($clue_id);

		$clue->consultant  = $this->user_model->get_user_by_id($clue->consultant);

		// 获取跟踪记录
		$clue->tracks = $this->sale_model->get_clue_tracks($clue_id);

		$this->_data['clue'] = $clue;
		$this->load->view('sale/clue_view',$this->_data);
	}

    // 线索编辑
    public function clue_edit()
    {
		$clue_id = $this->uri->segment(3);
        $this->_data['page_title'] = '编辑线索';
        // 获取单个跟踪
        $this->_data['clue'] = $this->sale_model->get_clue_by_id($clue_id);

		if (isset($_POST['save']))
		{
			if ($id = $this->_save_clue($_POST,$clue_id,'update'))
			{
				$this->session->set_flashdata('message',set_message('success','修改成功'));
				redirect('sale/clues','refresh');
			} else {
				$this->session->set_flashdata('message',set_message('error','修改失败'));

			}
		}

        $this->load->view('sale/clue_form',$this->_data);
    }

	// 跟踪记录
	public function tracks()
	{
		$this->_data['page_title'] = '跟踪记录';

		$this->_data['message'] = $this->session->flashdata('message');


		if(isset($_POST['save'])) {
			if($this->_save_track($_POST)) {

			}
		}

		$this->load->view('sale/track_lists',$this->_data);
	}

	public function clue_track()
	{
		if($this->_save_track($_POST)) {
			show_response(0,' 添加成功');
		} else {
			show_response(1,' 添加失败');
		}
	}

	// 跟踪记录 datatables
	public function track_db()
	{
		ob_start();

		$userTable              = 'bf_users';
		$userKey                = 'id';
		$trackTable              = 'bf_tracks';
		$clueTable              = 'bf_students';
		$clueKey              = 'student_id';

		$where["{$trackTable}.deleted"] = 0;

		$this->load->library('datatables');

		$this->load->helper('track_helper');

		if($this->_adminer['group'] == 4){
			$where["{$trackTable}.consultant"] = $this->_adminer['id'];
		}
		
		// $tracks = $this->track_model->join($userTable, "{$trackTable}.consultant = {$userTable}.{$userKey}", 'left')
		// 							->join($clueTable, "{$trackTable}.student_id = {$clueTable}.{$clueKey}", 'left')
		// 							->where($where)
		// 							->find_all();

		$this->datatables->select("{$trackTable}.track_id as track_id,{$trackTable}.student_id as student_id,{$clueTable}.student_name as student_name,
						{$trackTable}.title as title,{$trackTable}.content as content,	
						{$trackTable}.track_date as track_date,{$trackTable}.track_wish as track_wish,	
						{$trackTable}.next_track_date as next_track_date,{$trackTable}.track_result as track_result,
						{$userTable}.nickname")
									->join($clueTable, "{$trackTable}.student_id = {$clueTable}.{$clueKey}", 'left')
									->join($userTable, "{$trackTable}.consultant = {$userTable}.{$userKey}", 'left')
									->unset_column('student_id')
									->add_column('checkbox','<input type="checkbox" class="checkboxes" value="$1">','track_id')
									->edit_column('student_name','$1','generate_name(student_id,student_name)')
									->edit_column('track_wish','$1','generate_trade_wish(track_wish)')
									->edit_column('track_result','$1','generate_track_result(track_result)')
									->add_column('actions','$1','generate_actions(track_id,student_id)')
									->where($where)
									->from('tracks');

		echo $this->datatables->generate();
	}

    // 跟踪编辑
    public function track_edit()
    {
        $track_id = $this->uri->segment(3);
        $this->_data['page_title'] = '跟踪记录';
        // 获取单个跟踪
        $track = $this->sale_model->get_track_by_id($track_id);

        if(empty($track)) {
        	redirect('sale/tracks','refresh');
        }

         $this->_data['track'] = $track;

        $this->load->view('sale/track_form',$this->_data);
    }

    private function _save_clue($data,$id=0,$type='insert')
    {
    // $this->form_validation->set_rules($this->user_model->get_validation_rules($type));

    $this->form_validation->set_rules('student_name', 'lang:clue_student_name', 'required|trim|max_length[20]');
    $this->form_validation->set_rules('student_nickname', 'lang:clue_student_name', 'trim|max_length[20]');
	$this->form_validation->set_rules('grade', 'lang:clue_grade', 'trim|is_numeric|max_length[2]');
	$this->form_validation->set_rules('school', 'lang:clue_school', 'trim|max_length[1]');
    $this->form_validation->set_rules('parent_name1', 'lang:clue_parent_name1', 'trim|max_length[20]');
    $this->form_validation->set_rules('parent_phone1', 'lang:clue_parent_phone1', 'trim|is_numeric|max_length[11]');
    $this->form_validation->set_rules('parent_name2', 'lang:clue_sparent_name2', 'trim|max_length[20]');
    $this->form_validation->set_rules('parent_phone2', 'lang:clue_parent_phone2', 'trim|is_numeric|max_length[11]');
    $this->form_validation->set_rules('from_big', 'lang:from_big', 'trim|is_numeric|max_length[11]');
    $this->form_validation->set_rules('address', 'lang:clue_address', 'trim|max_length[255]');
    $this->form_validation->set_rules('phone', 'lang:clue_phone', 'required|trim|is_numeric|max_length[11]');
    $this->form_validation->set_rules('tel', 'lang:clue_tel', 'trim|max_length[20]');
    $this->form_validation->set_rules('idcard', 'lang:clue_idcard', 'trim|max_length[30]');
    $this->form_validation->set_rules('birthday', 'lang:clue_birthday', 'trim|max_length[20]');
    $this->form_validation->set_rules('sex', 'lang:clue_sex', 'trim|is_numeric|max_length[1]');
    $this->form_validation->set_rules('source', 'lang:clue_source', 'trim|is_numeric|max_length[1]');
    $this->form_validation->set_rules('trade_wish', 'lang:clue_trade_wish', 'required|trim|is_numeric|max_length[1]');
    $this->form_validation->set_rules('qq', 'lang:clue_qq', 'trim|is_numeric|max_length[15]');
    $this->form_validation->set_rules('weixin', 'lang:clue_weixin', 'trim|max_length[1]');
    $this->form_validation->set_rules('email', 'lang:bf_email', 'trim|valid_email|max_length[100]');


     $this->form_validation->set_rules('weibo', 'lang:clue_weibo', 'trim|max_length[255]');
        $this->form_validation->set_rules('consultant', 'lang:clue_consultant', 'trim|is_numeric|max_length[11]');
        $this->form_validation->set_rules('promoter', 'lang:clue_promoter', 'trim|is_numeric|max_length[2]');

        if ($this->form_validation->run() === false) {
        	$this->_error = validation_errors();
            return false;
        }

        unset($_POST['save']);

		$return = false;

        if ($type == 'insert') {
                if(isset($_POST['birthday'])&&empty($_POST['birthday'])) $_POST['birthday'] = NULL;
                $id = $this->sale_model->insert_clue($_POST);
    			$return = is_numeric($id);
        } elseif ($type == 'update') {
                if(isset($_POST['birthday'])&&empty($_POST['birthday'])) $_POST['birthday'] = NULL;
                $return = $this->sale_model->update_clue($id, $_POST);
        }

        if(!$return) {
        	$this->_error = '更新失败';
        	return false;
        }

        return $return;

    }

    private function _save_track($data = array(),$type='insert', $id=0)
    {
    // $this->form_validation->set_rules($this->user_model->get_validation_rules($type));

            $this->form_validation->set_rules('student_id', '学生id', 'required|trim|is_numeric(var)');
            $this->form_validation->set_rules('title', '标题', 'trim');
            $this->form_validation->set_rules('track_wish', '交易意愿', 'required|trim|is_numeric');
            $this->form_validation->set_rules('track_result', '跟踪结果', 'required|trim|is_numeric');
            $this->form_validation->set_rules('track_date', '跟踪时间', 'required|trim');
            $this->form_validation->set_rules('next_track_date', '下次跟踪日期', 'trim|max_length[100]');
            $this->form_validation->set_rules('content', '跟踪内容', 'trim');
            $this->form_validation->set_rules('consultant', '教育顾问', 'trim|is_numeric|max_length[2]');

            if ($this->form_validation->run() === false) {
                    return false;
            }

            unset($data['save']);

            if ($type == 'insert') {
                    if(isset($data['clue_next_track_date'])&&empty($data['clue_next_track_date'])) $_POST['clue_next_track_date'] = NULL;
                    $id = $this->sale_model->insert_track($data);
             		//更新线索信息
                    $data = array(
                            'last_contact_date'=>$data['track_date'],
                            'last_contact_result'=>$data['content'],
                            'next_contact_date'=>$data['next_track_date']);
					$this->sale_model->update_clue($_POST['student_id'],$data);

					$this->sale_model->update_clue($_POST['student_id'],array('contact_times'=>'contact_times+1'),FALSE);


				$return = is_numeric($id);
            } elseif ($type == 'update') {
                    $return = $this->sale_model->update_track($id, $data);
            }

            return $return;

    }


    // 上门确认
    public function do_visit() 
    {
    	$clue_id = (int)$this->input->post('id');

        if(is_numeric($clue_id))
        {
    		if($this->sale_model->update_clue($clue_id,array('status'=>1))) {
    			show_response(0,'操作成功');
    		} else {
     			show_response(1,'操作失败');   			
    		}
    	} else {
    			show_response(1,'参数非法');
    	}
    }

    // 转移线索
    public function clue_dist()
    {
    	$ids = $this->input->post('ids');

        $owner = (int)$this->input->post('owner');

//        $id_arr = explode(';', $ids);
		// 去除末尾的逗号
		$ids = substr($ids,-1)==','?substr($ids,0,-1):$ids;

        if(!empty($ids)&&!empty($owner)) {
			// 指定教学顾问
			$sql = 'update bf_students set consultant='.$owner.' where student_id in ('.$ids.')';

			$this->db->query($sql);
//        	foreach ($id_arr as $key => $id) {
//             	//更新线索
//            	$this->sale_model->update_clue($id,array('consultant'=>$owner));
//        	}
        	show_response(0,'操作成功');
        } else {
    		show_response(1,'参数非法');        	
        }
    }

	public function clue_import()
	{
		$name = $this->input->post('name');

		$filename = 'upload/files/'.$name;
	
//		$filename = 'upload/files/导入数据+市区初二年级-精准数据20190915（5970条）.xlsx';

		$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
		$cacheSettings = array();
		PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings);

		$objReader = PHPExcel_IOFactory::createReader('Excel2007');
		$objReader->setReadDataOnly(true);
		$objPHPExcel = $objReader->load($filename);
		$objWorksheet = $objPHPExcel->getActiveSheet();
		$highestRow = $objWorksheet->getHighestRow();
		$highestColumn = $objWorksheet->getHighestColumn();
		$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
		$excelData = array();

		$sql = "insert into bf_students(student_name,student_name_letter,phone,sex,grade,school,parent_name1,parent_phone1,created_on) values";

//		$py = new PYInitials();

		for ($row = 3; $row <= $highestRow; $row++) {
			// 日期

			// 学生姓名
			$student_name = (string)$objWorksheet->getCellByColumnAndRow(1, $row)->getValue();

//			$student_name_letter =  $py->getInitials($student_name)?strtoupper(substr($py->getInitials($student_name),0,1)):'';
			$student_name_letter = $student_name?strtoupper(substr(Pinyin_Pinyin::ChineseToPinyin($student_name, false, true),0,1)):'';
			// 家长姓名
			$parent_name = (string)$objWorksheet->getCellByColumnAndRow(2, $row)->getValue();

			// 年级
			$grade = (string)$objWorksheet->getCellByColumnAndRow(3, $row)->getValue();

			$grade = (int)array_search($grade,$this->config->item('comm.grade'));

			// 学校
			$school = (string)$objWorksheet->getCellByColumnAndRow(4, $row)->getValue();

			// 电话1
			$phone1 = (string)$objWorksheet->getCellByColumnAndRow(5, $row)->getValue();

			// 电话2
			$phone2 = (string)$objWorksheet->getCellByColumnAndRow(6, $row)->getValue();

			// 线索类型
			$clue_type = (string)$objWorksheet->getCellByColumnAndRow(7, $row)->getValue();

			// 性别
			$sex = (string)$objWorksheet->getCellByColumnAndRow(15, $row)->getValue();

			$sex = (int)array_search($sex,$this->config->item('comm.sex'));

			$str = sprintf("('%s','%s','%s',%d,%d,'%s','%s','%s',%d),",$student_name,$student_name_letter,$phone1,$sex,$grade,$school,$parent_name,$phone2,time());

			$sql .= $str;

//			for ($col = 0; $col < $highestColumnIndex; $col++) {
////				$exc$excelDataelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
//			}
		}

		$sql =  substr($sql,0,-1);

		$this->db->query($sql);

//		echo $sql;
		exit(json_encode(array('code'=>0,'msg'=>'导入成功')));
	}

	public function clue_del()
	{
		$id = $this->input->post('id');
//
		$this->db->where('student_id',$id);
		$this->db->delete('bf_students');

		// 删除该线索下的所有跟踪记录
		$this->db->where('student_id',$id);
		$this->db->delete('bf_tracks');

		exit(json_encode(array('code'=>0,'msg'=>'删除成功')));
	}

	public function track_del()
	{
		$id = $this->input->post('id');

		$this->db->where('track_id',$id);
		$this->db->delete('bf_tracks');

		exit(json_encode(array('code'=>0,'msg'=>'删除成功')));
	}

	// 转移线索
	public function track_dist()
	{
		$ids = $this->input->post('ids');

		$owner = (int)$this->input->post('owner');

//		$ids = explode(';', $ids);
//		// 去除末尾的逗号
		$ids = substr($ids,-1)==','?substr($ids,0,-1):$ids;

		if(!empty($ids)&&!empty($owner)) {
			// 指定教学顾问
			$sql = 'update bf_tracks set consultant='.$owner.' where track_id in ('.$ids.')';

			$this->db->query($sql);
//        	foreach ($id_arr as $key => $id) {
//             	//更新线索
//            	$this->sale_model->update_clue($id,array('consultant'=>$owner));
//        	}
			show_response(0,'操作成功');
		} else {
			show_response(1,'参数非法');
		}
	}

	// 文件上传
	public function file_upload()
	{
		$upload_handler = new UploadHandler();
	}


}	